import boto3
import os
import datetime
import time
from datetime import datetime
from datetime import timedelta
import openpyxl as xl
import sys
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.cell import MergedCell
import pandas as pd
import re
def main():
    year = str((datetime.today()).year)
    month = str((datetime.today()).month)
    # day=str((datetime.today()-timedelta(days = 1)).day)
    if len(month) == 1:
        month = '0'+month
    # if len(day)==1:
    #     day='0'+day
    # file_date=year+month+day
    file_date = year + month
    ACCESS_KEY="bvxeA8tEmvGVr0HccdOvzjY_RA6zlKa5WjxmCcO4"
    SECRET_KEY="rzJxd7F9vuTCM4cTxYCWS2wGu49Iyrezkrmpk7Kh"
    END_POINT="https://s3-qos.baocloud.cn"
    BUCKET_NAME="tbktjt0037"
    # BUCKET_PATH="onemill-app/liangangchang/ribao/"
    print(datetime.now().strftime('%Y%m%d%H%M%S'))

    # s3 = boto3.client('s3', aws_access_key_id=ACCESS_KEY, aws_secret_access_key=SECRET_KEY,endpoint_url=END_POINT)
    # s3.upload_file('T_ADS_FACT_STEEL_ENTP_MAIN_FINANCIAL_INDEX.csv', BUCKET_NAME, '202306/快报/T_ADS_FACT_STEEL_ENTP_MAIN_FINANCIAL_INDEX.csv')
    # s3 = boto3.client('s3', aws_access_key_id=ACCESS_KEY, aws_secret_access_key=SECRET_KEY, endpoint_url=END_POINT)
    # s3.upload_file('T_ADS_FACT_STEEL_ENTP_TONS_COSTS_ANALYSE.csv', BUCKET_NAME, '202305/快报/T_ADS_FACT_STEEL_ENTP_TONS_COSTS_ANALYSE.csv')

    s3 = boto3.resource('s3', aws_access_key_id=ACCESS_KEY, aws_secret_access_key=SECRET_KEY,endpoint_url=END_POINT)
    my_bucket = s3.Bucket(BUCKET_NAME)
    key_list = []
    for obj in my_bucket.objects.all():
        print(obj.key)
        string1 = obj.key
        key_list.append(string1)





        # print(type(obj.key))
    #     string1 = obj.key
    #     regex = r'^.*(\d{4}年\d+月).*\.xlsx$'
    #     match = re.match(regex, string1)
    #     if match:
    #         date_str = match.group(1)
    #         print(f'{string1} 匹配成功，日期为 {date_str}')
    #         key_list.append(string1)
    #         filename = string1
    #         print('match成功')
    #     else:
    #         filename = '202304/快报/2023年4月财务快报反馈初稿.xlsx'
    # # filename = '202304/快报/2023年4月财务快报反馈初稿.xlsx'
    # print(filename[0:6])
    #
    # match = re.search(r'\d+年\d+月', filename)
    # if match:
    #     result = match.group()
    #     print(result)
    #     if len(result) == 8:
    #         result0 = result[0:4] + result[5:7]
    #     elif len(result) == 7:
    #         result0 = result[0:4] +'0' + result[5:6]
    #     else:
    #         result0 = filename[0:6]
    #
    # else:
    #     result0 = filename[0:6]
    # print(result0)
    #
    s3 = boto3.client('s3', aws_access_key_id=ACCESS_KEY, aws_secret_access_key=SECRET_KEY, endpoint_url=END_POINT)
    print(dir(s3))
    # # s3.download_file(BUCKET_NAME, '202304/快报/T_ADS_FACT_STEEL_ENTP_MAIN_FINANCIAL_INDEX.csv', 'D:/repos/sicost/2222T_ADS_FACT_STEEL_ENTP_MAIN_FINANCIAL_INDEX.csv')
    #
    s3.download_file(BUCKET_NAME, '202306/快报/T_ADS_FACT_ENTP_PROFIT_TOP.csv', 'D:/repos/T_ADS_FACT_ENTP_PROFIT_TOP_6.csv')
    s3.download_file(BUCKET_NAME, '202305/快报/T_ADS_FACT_ENTP_PROFIT_TOP.csv', 'D:/repos/T_ADS_FACT_ENTP_PROFIT_TOP_5.csv')

    # # s3.download_file('my-bucket-name', 'path/to/my-file.txt', '/local/path/to/save/my-file.txt')
    # #第一个参数是存储桶(bucket)的名称；第二个参数是要下载的对象的键(key)，它可以包括路径(path)信息；第三个参数是要保存到本地的文件名(filename)，包括本地路径信息。
    # print('finish')

    # wb = xl.load_workbook("2023年5月钢协反馈(1).xlsx")
    # result0 = '202305'
    wb = xl.load_workbook("2023年6月钢协反馈(1).xlsx")
    result0 = '202306'
    # wb = xl.load_workbook("2023年4月财务快报反馈初稿.xlsx")
    # wb = xl.load_workbook("Benchmarks.xlsx")
    # sheet_ = wb["打印1 "]
    print(wb.sheetnames)
    for sheet_name in wb.sheetnames:
        print(sheet_name)
    sheet_name_list = []
    sheet_name = '大中型钢铁企业主要财务指标汇总表'
    sheet_name_list.append(sheet_name)
    sheet_name = '利润总额前20名企业情况表'
    sheet_name_list.append(sheet_name)
    # sheet_name = '吨钢利润,吨钢费用'
    sheet_name = '吨钢利润'

    sheet_name_list.append(sheet_name)
    print(sheet_name_list)
    # sheet_name = wb.sheetnames[20]
    # print(sheet_name)
    for tmp_sheet_name in sheet_name_list:
        print(tmp_sheet_name)
        df = sheetname_2_df(wb, tmp_sheet_name)
        if tmp_sheet_name == '大中型钢铁企业主要财务指标汇总表':
            mapping = {'序号': 'SERIAL_NUM',
                       '项目': 'INDEX_NAME',
                       '本月数': 'CURR_MONTH_VALUE',
                       '本年累计': 'CURR_YEAR_VALUE',
                       '去年同期': 'LAST_YEAR_VALUE',
                       '增减(%)': 'RATIO_VALUE'}
            df.rename(columns=mapping, inplace=True)
            tmp_now = datetime.now().strftime('%Y%m%d%H%M%S')

            new_df = df.reset_index()
            print(new_df['index'].dtype)
            new_df.rename(columns={'index': 'random_id'}, inplace=True)
            print(new_df['random_id'].dtype)
            new_df['new_tag'] = new_df['random_id'].astype(str)
            print(new_df['new_tag'].dtype)
            print(new_df)
            new_df['REC_ID'] = new_df.apply(lambda x: str(tmp_now) + '-' + str(x['new_tag']), axis=1)
            new_df['LOADER'] = 'system'
            new_df['LOAD_TIME'] = tmp_now
            new_df['DATE_TIME'] = result0
            new_df.drop(['random_id'], axis=1, inplace=True)
            new_df.drop(['new_tag'], axis=1, inplace=True)
            new_df[['CURR_MONTH_VALUE', 'CURR_YEAR_VALUE', 'LAST_YEAR_VALUE', 'RATIO_VALUE']] = new_df[['CURR_MONTH_VALUE', 'CURR_YEAR_VALUE', 'LAST_YEAR_VALUE', 'RATIO_VALUE']].apply(pd.to_numeric, errors='coerce')


            desired_order = ['REC_ID', 'LOADER', 'LOAD_TIME', 'DATE_TIME', 'SERIAL_NUM', 'INDEX_NAME', 'CURR_MONTH_VALUE', 'CURR_YEAR_VALUE', 'LAST_YEAR_VALUE', 'RATIO_VALUE']
            # 使用 reindex 方法重新排序 DataFrame 的列
            new_df = new_df.reindex(columns=desired_order)
            new_df.to_csv('T_ADS_FACT_STEEL_ENTP_MAIN_FINANCIAL_INDEX' + '.csv', index=False)
            # s3 = boto3.client('s3', aws_access_key_id=ACCESS_KEY, aws_secret_access_key=SECRET_KEY,endpoint_url=END_POINT)
            # s3.upload_file('T_ADS_FACT_STEEL_ENTP_MAIN_FINANCIAL_INDEX.csv', BUCKET_NAME, '202304/快报/T_ADS_FACT_STEEL_ENTP_MAIN_FINANCIAL_INDEX.csv')
            print('FINISH')

        if tmp_sheet_name == '利润总额前20名企业情况表':
            mapping = {'序号': 'SERIAL_NUM',
                       '单位': 'COMPANY_NAME',
                       '本月数': 'CURR_MONTH_VALUE',
                       '本年累计': 'CURR_YEAR_VALUE',
                       '去年同期': 'LAST_YEAR_VALUE',
                       '增减(%)': 'RATIO_VALUE'}
            df.rename(columns=mapping, inplace=True)

            tmp_now2 = datetime.now().strftime('%Y%m%d%H%M%S')

            new_df = df.reset_index()
            print(new_df['index'].dtype)
            new_df.rename(columns={'index': 'random_id'}, inplace=True)
            print(new_df['random_id'].dtype)
            new_df['new_tag'] = new_df['random_id'].astype(str)
            print(new_df['new_tag'].dtype)
            print(new_df)
            new_df['REC_ID'] = new_df.apply(lambda x: str(tmp_now2) + '-' + str(x['new_tag']), axis=1)
            new_df['LOADER'] = 'system'
            new_df['LOAD_TIME'] = tmp_now2
            new_df['DATE_TIME'] = result0
            new_df.drop(['random_id'], axis=1, inplace=True)
            new_df.drop(['new_tag'], axis=1, inplace=True)

            new_df[['CURR_MONTH_VALUE', 'CURR_YEAR_VALUE', 'LAST_YEAR_VALUE']] = new_df[
                ['CURR_MONTH_VALUE', 'CURR_YEAR_VALUE', 'LAST_YEAR_VALUE']].apply(pd.to_numeric, errors='coerce')
            new_df['RATIO_VALUE'] = new_df['RATIO_VALUE'].astype(str)
            desired_order = ['REC_ID', 'LOADER', 'LOAD_TIME', 'DATE_TIME', 'SERIAL_NUM', 'COMPANY_NAME', 'CURR_MONTH_VALUE',
                             'CURR_YEAR_VALUE', 'LAST_YEAR_VALUE', 'RATIO_VALUE']
            # 使用 reindex 方法重新排序 DataFrame 的列
            new_df = new_df.reindex(columns=desired_order)
            new_df.to_csv('T_ADS_FACT_ENTP_PROFIT_TOP' + '.csv', index=False)
            print('FINISH')

            s3 = boto3.client('s3', aws_access_key_id=ACCESS_KEY, aws_secret_access_key=SECRET_KEY, endpoint_url=END_POINT)
            s3.upload_file('T_ADS_FACT_ENTP_PROFIT_TOP.csv', BUCKET_NAME, '202306/快报/T_ADS_FACT_ENTP_PROFIT_TOP.csv')
        if tmp_sheet_name == '吨钢利润,吨钢费用' or tmp_sheet_name == '吨钢利润':
            mapping = {'序号': 'SERIAL_NUM',
                       '单位': 'COMPANY_NAME',
                       '吨钢利润本月数': 'CURR_MONTH_VALUE',
                       '吨钢利润本年累计': 'CURR_YEAR_VALUE',
                       '吨钢利润去年同期': 'LAST_YEAR_VALUE',
                       '吨钢利润增减量': 'RATIO_VALUE'}
            df.rename(columns=mapping, inplace=True)
            # df['SERIAL_NUM_STR'] = str(df['SERIAL_NUM'])
            df['SERIAL_NUM_STR'] = df['SERIAL_NUM'].astype(str)

            # a1 = df.iloc[0]['SERIAL_NUM_STR']
            # a2 = df.iloc[1]['SERIAL_NUM_STR']
            # a3 = df.iloc[2]['SERIAL_NUM_STR']
            # a4 = df.iloc[3]['SERIAL_NUM_STR']
            # a5 = df.iloc[4]['SERIAL_NUM_STR']
            # a6 = df.iloc[5]['SERIAL_NUM_STR']
            # print(type(df.iloc[0]['SERIAL_NUM_STR']))
            # print(df.iloc[1]['SERIAL_NUM_STR'])
            # print(type(df.iloc[1]['SERIAL_NUM_STR']))
            # print(df.iloc[2]['SERIAL_NUM_STR'])
            # print(type(df.iloc[2]['SERIAL_NUM_STR']))
            # print(df.iloc[3]['SERIAL_NUM_STR'])
            # print(type(df.iloc[3]['SERIAL_NUM_STR']))

            def __cal_strincludenum(x):
                if '0' in x.SERIAL_NUM_STR:
                    rst = 1
                elif '1' in x.SERIAL_NUM_STR:
                    rst = 1
                elif '2' in x.SERIAL_NUM_STR:
                    rst = 1
                elif '3' in x.SERIAL_NUM_STR:
                    rst = 1
                elif '4' in x.SERIAL_NUM_STR:
                    rst = 1
                elif '5' in x.SERIAL_NUM_STR:
                    rst = 1
                elif '6' in x.SERIAL_NUM_STR:
                    rst = 1
                elif '7' in x.SERIAL_NUM_STR:
                    rst = 1
                elif '8' in x.SERIAL_NUM_STR:
                    rst = 1
                elif '9' in x.SERIAL_NUM_STR:
                    rst = 1
                else:
                    rst = 0
                return rst

            df['include_type'] = df.apply(lambda x: __cal_strincludenum(x), axis=1)
            df = df[df['include_type']==1]

            df.drop(['SERIAL_NUM_STR'], axis=1, inplace=True)
            df.drop(['include_type'], axis=1, inplace=True)

            # df = df[df['SERIAL_NUM']>0]
            tmp_now2 = datetime.now().strftime('%Y%m%d%H%M%S')

            new_df = df.reset_index()
            print(new_df['index'].dtype)
            new_df.rename(columns={'index': 'random_id'}, inplace=True)
            print(new_df['random_id'].dtype)
            new_df['new_tag'] = new_df['random_id'].astype(str)
            print(new_df['new_tag'].dtype)
            print(new_df)
            new_df['REC_ID'] = new_df.apply(lambda x: str(tmp_now2) + '-' + str(x['new_tag']), axis=1)
            new_df['LOADER'] = 'system'
            new_df['LOAD_TIME'] = tmp_now2
            new_df['DATE_TIME'] = result0
            new_df.drop(['random_id'], axis=1, inplace=True)
            new_df.drop(['new_tag'], axis=1, inplace=True)

            new_df[['CURR_MONTH_VALUE', 'CURR_YEAR_VALUE', 'LAST_YEAR_VALUE']] = new_df[['CURR_MONTH_VALUE', 'CURR_YEAR_VALUE', 'LAST_YEAR_VALUE']].apply(pd.to_numeric, errors='coerce')
            new_df['RATIO_VALUE'] = new_df['RATIO_VALUE'].astype(str)
            a1 = new_df.iloc[0]['RATIO_VALUE']
            a2 = new_df.iloc[1]['RATIO_VALUE']
            a3 = new_df.iloc[2]['RATIO_VALUE']
            a4 = new_df.iloc[3]['RATIO_VALUE']
            a5 = new_df.iloc[4]['RATIO_VALUE']
            a6 = new_df.iloc[5]['RATIO_VALUE']
            print(type(new_df.iloc[0]['RATIO_VALUE']))
            print(new_df.iloc[1]['RATIO_VALUE'])
            print(type(new_df.iloc[1]['RATIO_VALUE']))
            print(new_df.iloc[2]['RATIO_VALUE'])
            print(type(new_df.iloc[2]['RATIO_VALUE']))
            print(new_df.iloc[3]['RATIO_VALUE'])
            print(type(new_df.iloc[3]['RATIO_VALUE']))
            desired_order = ['REC_ID', 'LOADER', 'LOAD_TIME', 'DATE_TIME', 'SERIAL_NUM', 'COMPANY_NAME', 'CURR_MONTH_VALUE',
                             'CURR_YEAR_VALUE', 'LAST_YEAR_VALUE', 'RATIO_VALUE']
            # 使用 reindex 方法重新排序 DataFrame 的列
            new_df = new_df.reindex(columns=desired_order)
            new_df.to_csv('T_ADS_FACT_STEEL_ENTP_TONS_COSTS_ANALYSE' + '.csv', index=False)
            print('FINISH')

        # df.to_csv('output_0530_' + tmp_sheet_name + '.csv', index=False)

    # s3 = boto3.client('s3', aws_access_key_id=ACCESS_KEY, aws_secret_access_key=SECRET_KEY,endpoint_url=END_POINT)
    # s3.upload_file('output_0530_1.csv', BUCKET_NAME, '202304/快报/output_0530_1.csv')
    # df.rename(columns={'sum': 'NEW_TOT_IN_WT_TMP'}, inplace=True)

    # mapping = {'序号': 'SERIAL_NUM',
    #            '项目': 'INDEX_NAME',
    #            '本月数': 'CURR_MONTH_VALUE',
    #            '本年累计': 'CURR_YEAR_VALUE',
    #            '去年同期': 'LAST_YEAR_VALUE',
    #            '增减(%)': 'RATIO_VALUE'}
    # df.rename(columns=mapping, inplace=True)
    # mapping2 = {'序号': 'SERIAL_NUM',
    #             '单位': 'COMPANY_NAME',
    #             '本月数': 'CURR_MONTH_VALUE',
    #             '本年累计': 'CURR_YEAR_VALUE',
    #             '去年同期': 'LAST_YEAR_VALUE',
    #             '增减(%)': 'RATIO_VALUE'}

def get_ord_list(str):

    return [ord(i) for i in str]
def calcu_approx(str1, str2):
    def dot(A, B):
        return (sum(a * b for a, b in zip(A, B)))
    def cosine_similarity(a, b):
        return dot(a, b) / ((dot(a, a) ** .5) * (dot(b, b) ** .5))
    ord_list1 = get_ord_list(str1)
    ord_list2 = get_ord_list(str2)
    max_ord = max(max(ord_list1), max(ord_list2))+1
    ord_dense_list1 = [0]*max_ord
    ord_dense_list2 = [0]*max_ord
    for i in ord_list1:
        ord_dense_list1[i] = 1
    for i in ord_list2:
        ord_dense_list2[i] = 1
    return cosine_similarity(ord_dense_list1, ord_dense_list2)

def add_column_remark(list):
    set_list = set(list)
    if len(set_list) == len(list):
        print('无重复，不需要拼接')
    else:
        print('有重复，需要拼接')
    list2 = []
    # print(len(list))
    for i in range(0, len(list)):
        # print(list.count(list[i]))
        # print(list[i])
        if list.count(list[i]) == 1:
            list2.append(list[i])
        else:
            # print('重复元素出现的位置索引分别是 = ', [j for j, v in enumerate(list) if v == list[i]])
            chongfuidlist = [j for j, v in enumerate(list) if v == list[i]]
            chongfulist = []
            for l in range(1, chongfuidlist[0] + 1):
                # print('l')
                # print(l)
                for k in chongfuidlist:
                    if k - l >= 0:
                        # print(k)
                        # print(list[k])
                        # print(list[k - l] + list[k])
                        chongfulist.append(list[k - l] + list[k])
                    else:
                        chongfulist.append(list[k])
                if len(set(chongfulist)) == len(chongfulist):
                    # print('无重复')
                    # print(list[i - l] + list[i])
                    list2.append(list[i - l] + list[i])
                    break
                else:
                    # print('有重复')
                    chongfulist = []
    return list2


def sheetname_2_df(wb,sheet_name):
    sheet_ = wb[sheet_name]
    max_row_num = sheet_.max_row  # 获取最大行数
    max_col_num = sheet_.max_column  # 获取最大列数
    data_list = []
    merge_tag_list = []
    title_list = []
    title_num = 0
    column_num = 0
    hastitlebutnocolumn = 0
    df_num = 1
    need_merge = 0
    my_dict = {}
    my_dict2 = {}
    need_merge_list = []
    for row_index in range(1, max_row_num + 1):
        print('现在解析行数：', row_index)
        col_list = []
        line_type = ''
        merge_tag_sum0 = 0
        none_num_sum0 = 0
        for col_index in range(1, max_col_num + 1):
            merge_tag0 = 0
            none_num0 = 0
            cell0 = sheet_.cell(row=row_index, column=col_index)
            if isinstance(cell0, MergedCell):
                merge_tag0 = 1
            if cell0.value is None:
                none_num0 = 1
            none_num_sum0 = none_num_sum0 + none_num0
            merge_tag_sum0 = merge_tag_sum0 + merge_tag0
        if none_num_sum0 == max_col_num and title_num == 0:
            print('该行为空行')
            print('将sheet名赋值给title')
            line_type = 'title'
            title_num = title_num + 1
            title_name = sheet_name

            title_list.append(sheet_.cell(row=row_index, column=1).value)
            continue
        elif none_num_sum0 != max_col_num and title_num == 0:
            print('非空行默认为标题')
            for col_index in range(1, max_col_num + 1):
                cell = sheet_.cell(row=row_index, column=col_index)
                if cell.value is not None:
                    line_type = 'title'
                    title_num = title_num + 1
                    title_name = cell.value
                    title_list.append(sheet_.cell(row=row_index, column=1).value)
                    break
                else:
                    continue
        # if row_index == 1:
        #     merge_tag_sum = 0
        #     none_num_sum = 0
        #     for col_index in range(1, max_col_num + 1):
        #         merge_tag = 0
        #         none_num = 0
        #         cell = sheet_.cell(row=row_index, column=col_index)
        #         if isinstance(cell, MergedCell):
        #             merge_tag = 1
        #         if cell.value is None:
        #             none_num = 1
        #         none_num_sum = none_num_sum + none_num
        #         merge_tag_sum = merge_tag_sum + merge_tag
        #         col_list.append(cell.value)
        #     if '大中' in sheet_.cell(row=row_index, column=1).value and none_num_sum == max_col_num - 1:
        #         line_type = 'title'
        #         title_num = title_num + 1
        #         title_name = sheet_.cell(row=row_index, column=1).value
        #         title_list.append(sheet_.cell(row=row_index, column=1).value)
        #         #暂时默认首行必定为title
        else:
            col_list = []
            merge_tag_sum = 0
            none_num_sum = 0
            for col_index in range(1, max_col_num + 1):
                merge_tag = 0
                none_num = 0
                cell = sheet_.cell(row=row_index, column=col_index)
                if isinstance(cell, MergedCell):
                    merge_tag = 1
                if cell.value is None:
                    none_num = 1
                none_num_sum = none_num_sum + none_num
                merge_tag_sum = merge_tag_sum + merge_tag
                col_list.append(cell.value)
            remark_num_sum = 0
            res = []
            for i in col_list:
                if type(i) == str:
                    i = i.replace(" ", "")
                    if i[0:2] in ['表1', '表2', '表3', '表4', '表5', '表6', '表7', '表8', '表9', '备：', '备:', '注：', '注:'] or (
                            len(i) > 2 and i[0:2] in ['单位', '备注']):
                        line_type = 'remark'
                if i is not None:
                    res.append(i)
            if line_type == 'remark':
                continue
            set_res = set(res)
            if len(set_res) == len(res):
                print('去除None后行list无重复')
            else:
                print('去除None后行list有重复')
            if title_num > column_num:
                hastitlebutnocolumn = 1
            else:
                hastitlebutnocolumn = 0
            if merge_tag_sum == 0 and len(set_res) == len(res) and hastitlebutnocolumn == 1:
                line_type = 'column'
                column_num = column_num + 1
                column_list = res
                # print(len(column_list))
            elif merge_tag_sum == 0 and len(set_res) != len(res) and hastitlebutnocolumn == 1:
                print('columnlist有重复数据需要添加标签去重')
                list_new = add_column_remark(res)
                line_type = 'column'
                column_num = column_num + 1
                column_list = list_new
                print('当前column_list为:')
                print(column_list)
                # print(len(column_list))
            elif merge_tag_sum != 0 and hastitlebutnocolumn == 0:
                print('出现合并单元格应该是纵向！！！合并格，已经生成的column应该作废，与本行合并共同作为column')
                print('重置column')
                col_list = []
                for col_index in range(1, max_col_num + 1):
                    merge_tag = 0
                    none_num = 0
                    cell = sheet_.cell(row=row_index, column=col_index)
                    if isinstance(cell, MergedCell):
                        merge_tag = 1
                    if cell.value is None:
                        none_num = 1
                    none_num_sum = none_num_sum + none_num
                    merge_tag_sum = merge_tag_sum + merge_tag
                    value1 = column_list[col_index - 1]
                    value2 = cell.value
                    if type(value1) == str:
                        value1 = value1.replace(" ", "")
                    if type(value2) == str:
                        value2 = value2.replace(" ", "")
                    if value1 is None:
                        value1 = ''
                    if value2 is None:
                        value2 = ''
                    value = str(value1) + str(value2)
                    col_list.append(value)
                res = []
                for i in col_list:
                    if type(i) == str:
                        i = i.replace(" ", "")
                    res.append(i)
                if len(set(res)) != len(res):
                    res = add_column_remark(res)
                line_type = 'column'
                column_num = column_num - 1
                column_num = column_num + 1
                column_list = res
                print('当前column_list为:')
                print(column_list)
                # print(len(column_list))
            elif need_merge == 0 and merge_tag_sum != 0 and hastitlebutnocolumn == 1:
                print('出现合并单元格应该是横向!!!合并格，并且该行可能是需要拼接下一行共同作为column')
                need_merge = 1
                col_list = []
                for col_index in range(1, max_col_num + 1):
                    merge_tag = 0
                    none_num = 0
                    cell = sheet_.cell(row=row_index, column=col_index)
                    if isinstance(cell, MergedCell):
                        merge_tag = 1
                        for merged_range in sheet_.merged_cell_ranges:
                            if cell.coordinate in merged_range:
                                cell = sheet_.cell(row=merged_range.min_row, column=merged_range.min_col)
                                break
                    if cell.value is None:
                        none_num = 1
                    none_num_sum = none_num_sum + none_num
                    merge_tag_sum = merge_tag_sum + merge_tag
                    col_list.append(cell.value)
                # print(col_list)
                need_merge_list = col_list
            elif need_merge == 1 and hastitlebutnocolumn == 1:
                print('需要merge单元格生成column，横赋值，纵不赋值，两行合并')
                col_list = []
                for col_index in range(1, max_col_num + 1):
                    merge_tag = 0
                    none_num = 0
                    cell = sheet_.cell(row=row_index, column=col_index)
                    if isinstance(cell, MergedCell):
                        merge_tag = 1
                    if cell.value is None:
                        none_num = 1
                    none_num_sum = none_num_sum + none_num
                    merge_tag_sum = merge_tag_sum + merge_tag
                    # print(sheet_.cell(row=row_index-1, column=col_index).value)
                    value1 = need_merge_list[col_index - 1]
                    value2 = cell.value
                    if type(value1) == str:
                        value1 = value1.replace(" ", "")
                    if type(value2) == str:
                        value2 = value2.replace(" ", "")
                    if value1 is None:
                        value1 = ''
                    if value2 is None:
                        value2 = ''
                    value = str(value1) + str(value2)
                    col_list.append(value)
                need_merge_list = []
                need_merge = 0
                res = []
                for i in col_list:
                    if type(i) == str:
                        i = i.replace(" ", "")
                    res.append(i)
                if len(set(res)) != len(res):
                    res = add_column_remark(res)
                line_type = 'column'
                column_num = column_num + 1
                column_list = res
                print('当前column_list为:')
                print(column_list)
                # print(column_list)
            elif merge_tag_sum == 0 and hastitlebutnocolumn == 0:
                print(type(col_list[0]))
                print(col_list[0])
                # print([ord(i) for i in str])
                if type(col_list[0]) == str:
                    if title_list[0] is None or col_list[0] is None:
                        cos = 0
                    else:
                        cos = calcu_approx(title_list[0], col_list[0])
                    print(cos)
                    if cos > 0.5:
                        print('和title相似度高，是title')
                        line_type = 'title'
                        title_num = title_num + 1
                        # print(title_name)
                        # print(data_list)
                        # print(column_list)
                        df = pd.DataFrame(data_list, columns=column_list)
                        i = df_num
                        my_dict[f"df_{i}"] = pd.DataFrame(data_list, columns=column_list)
                        my_dict2[f"df_1{i}"] = pd.DataFrame(data_list, columns=column_list)
                        df_num = df_num + 1
                        # 重新新开一个df
                        data_list = []
                        column_list = []
                        title_name = col_list[0]
                        title_list.append(col_list[0])
                    else:
                        print('和title相似度不高，不是title')
                        line_type = 'data'
                        data_list.append(col_list[0:len(column_list)])
                else:
                    if none_num_sum == max_col_num:
                        print('空行无意义')
                        break
                    else:
                        line_type = 'data'
                        data_list.append(col_list[0:len(column_list)])

    # print(title_name)
    # print(data_list)
    df = pd.DataFrame(data_list, columns=column_list)
    i = df_num
    my_dict[f"df_{i}"] = pd.DataFrame(data_list, columns=column_list)
    my_dict2[f"df_1{i}"] = pd.DataFrame(data_list, columns=column_list)

    merge_id_list = []
    for m in range(0, len(title_list)):
        # print(title_list[0:m])
        new_list = title_list[0:m]
        if title_list[m] in new_list:
            print('title一样，需要合并df')
            # print(m)
            idlist = [j for j, v in enumerate(new_list) if v == title_list[m]]
            # print(idlist)
            merge_title_list_id = idlist[0]
            v = []
            col = my_dict.get(f"df_{merge_title_list_id + 1}").columns.values.tolist()
            v.append(col[0])
            my_dict2[f"df_1{merge_title_list_id + 1}"] = pd.merge(my_dict2.get(f"df_1{merge_title_list_id + 1}"),
                                                                  my_dict.get(f"df_{m + 1}"), on=v, how='left')
            merge_id_list.append(m)
            # print(my_dict2)
    # print(merge_id_list)
    for x in reversed(merge_id_list):
        del my_dict2[f"df_1{x + 1}"]
        del title_list[x]
    # for m in range(0, len(title_list)):
    #     print(title_list[m])
    #     # print(list(my_dict2.keys())[m])
    #     df_name = list(my_dict2.keys())[m]
    #     print(my_dict2.get(f"{df_name}"))
    #     df = my_dict2.get(f"{df_name}")
    #     # df.to_csv('output_0526_' + sheet_name + str(m) + '.csv', index=False)
    m = 0
    print(title_list[m])
    # print(list(my_dict2.keys())[m])
    df_name = list(my_dict2.keys())[m]
    print(my_dict2.get(f"{df_name}"))
    df = my_dict2.get(f"{df_name}")
    return df



if __name__ == '__main__':
    start1 = datetime.now()
    status = main()
    elapsed2 = float((datetime.now() - start1).seconds)
    print("Time Used 4 All ----->>>> %f seconds" % (elapsed2))

    sys.exit(0)